[9]:
import pandas as pd
import mysql.connector
import matplotlib.pyplot as plt
import seaborn as sns
%matplotlib
Using matplotlib backend: module://matplotlib_inline.backend_inline
### Basic Python Script to Connect and Analyze
Basic Python Script to Connect and Analyze¶
[3]:
# ✅ MySQL connection
conn = mysql.connector.connect(
host="localhost",
user="root",
password="Root",
database="phonepe"
)
# ✅ SQL Query
query = """
SELECT year, SUM(transaction_amount) AS total_amount
FROM aggregated_transaction
GROUP BY year
ORDER BY year;
"""
df = pd.read_sql(query, conn)
conn.close()
# ✅ Plot
plt.figure(figsize=(8,5))
plt.plot(df['year'], df['total_amount'], marker='o', color='purple')
plt.title("💰 Total Transactions Per Year")
plt.xlabel("Year")
plt.ylabel("Total Amount (₹)")
plt.grid(True)
plt.tight_layout()
plt.show()
C:\Users\LENOVO\AppData\Local\Temp\ipykernel_4080\3934802578.py:17: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy. df = pd.read_sql(query, conn) C:\Users\LENOVO\AppData\Local\Temp\ipykernel_4080\3934802578.py:27: UserWarning: Glyph 128176 (\N{MONEY BAG}) missing from font(s) DejaVu Sans. plt.tight_layout() C:\Users\LENOVO\anaconda3\Lib\site-packages\IPython\core\pylabtools.py:170: UserWarning: Glyph 128176 (\N{MONEY BAG}) missing from font(s) DejaVu Sans. fig.canvas.print_figure(bytes_io, **kw)
### Top 10 States by Total Transactions
Top 10 States by Total Transactions¶
[11]:
query = """
SELECT state, SUM(transaction_amount) AS total_amount
FROM aggregated_transaction
GROUP BY state
ORDER BY total_amount DESC
LIMIT 10;
"""
df = pd.read_sql(query, conn)
# Plot
plt.figure(figsize=(10,6))
plt.barh(df['state'], df['total_amount'], color='skyblue')
plt.gca().invert_yaxis() # Highest on top
plt.title("🏆 Top 10 States by Total Transaction Amount")
plt.xlabel("Amount (₹)")
plt.tight_layout()
plt.show()
C:\Users\LENOVO\AppData\Local\Temp\ipykernel_4080\2086232993.py:9: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy. df = pd.read_sql(query, conn)
--------------------------------------------------------------------------- OperationalError Traceback (most recent call last) Cell In[11], line 9 1 query = """ 2 SELECT state, SUM(transaction_amount) AS total_amount 3 FROM aggregated_transaction (...) 6 LIMIT 10; 7 """ ----> 9 df = pd.read_sql(query, conn) 11 # Plot 12 plt.figure(figsize=(10,6)) File ~\anaconda3\Lib\site-packages\pandas\io\sql.py:706, in read_sql(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize, dtype_backend, dtype) 704 with pandasSQL_builder(con) as pandas_sql: 705 if isinstance(pandas_sql, SQLiteDatabase): --> 706 return pandas_sql.read_query( 707 sql, 708 index_col=index_col, 709 params=params, 710 coerce_float=coerce_float, 711 parse_dates=parse_dates, 712 chunksize=chunksize, 713 dtype_backend=dtype_backend, 714 dtype=dtype, 715 ) 717 try: 718 _is_table_name = pandas_sql.has_table(sql) File ~\anaconda3\Lib\site-packages\pandas\io\sql.py:2738, in SQLiteDatabase.read_query(self, sql, index_col, coerce_float, parse_dates, params, chunksize, dtype, dtype_backend) 2727 def read_query( 2728 self, 2729 sql, (...) 2736 dtype_backend: DtypeBackend | Literal["numpy"] = "numpy", 2737 ) -> DataFrame | Iterator[DataFrame]: -> 2738 cursor = self.execute(sql, params) 2739 columns = [col_desc[0] for col_desc in cursor.description] 2741 if chunksize is not None: File ~\anaconda3\Lib\site-packages\pandas\io\sql.py:2672, in SQLiteDatabase.execute(self, sql, params) 2670 raise TypeError("Query must be a string unless using sqlalchemy.") 2671 args = [] if params is None else [params] -> 2672 cur = self.con.cursor() 2673 try: 2674 cur.execute(sql, *args) File ~\anaconda3\Lib\site-packages\mysql\connector\connection_cext.py:841, in CMySQLConnection.cursor(self, buffered, raw, prepared, cursor_class, dictionary, read_timeout, write_timeout) 839 self.handle_unread_result(prepared) 840 if not self.is_connected(): --> 841 raise OperationalError("MySQL Connection not available.") 842 if read_timeout or write_timeout: 843 warnings.warn( 844 """The use of read_timeout after the connection has been established is unsupported 845 in the C-Extension""", 846 category=Warning, 847 ) OperationalError: MySQL Connection not available.
[ ]:
Notebook cell shifted up successfully